/** 
 * @Title: SenDao.java 
 * @Package com.zizo.autohome.sentence.dao 
 * @Description: TODO
 * @author HeYan
 * @date 2016年10月17日 下午5:19:24 
 */
package com.zizo.autohome.sentence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.autohome.emotion.alg.AhoCorasickDoubleArrayTrie;
import com.zizo.autohome.sentence.util.SentenceUtil;
import com.zizo.util.ConnectionPool;

/**
 * @ClassName: SenDao
 * @Description: TODO 给定具体的词，找出句子
 * @author HeYan
 * @date 2016年10月17日 下午5:19:24
 * 
 */
public final class SenDao implements Runnable {
	private int carid;// 论坛ID
	private Connection conn168;// 数据库连接
	private Connection conn198;// 数据库连接
	private String tablename;// 表名称
	private int count;// 关键词的数量
	private int count2;
	private String database;
	private List<Integer> years;
	private boolean bbs;
	private boolean kb;
	private boolean isFilter;
	private Map<String, String> map;// 城市的级别
	private Map<Integer, List<Integer>> topics;// 所有主题集合的ID
	private Map<Integer, List<String>> topicMap;// 所有主题的集合
	private AhoCorasickDoubleArrayTrie<Integer> acdatLocal;
	private Map<Integer, List<Integer>> topics2;// 所有主题集合的ID
	private Map<Integer, List<String>> topicMap2;// 所有主题的集合
	private AhoCorasickDoubleArrayTrie<Integer> acdatLocal2;
	private Map<Integer, String> comment1;
	private Map<Integer, String> comment2;

	/**
	 * @Title: getSynData
	 * @Description: 得到对应的carid 论坛的标题和内容
	 * @return void
	 * @author Alexis
	 */
	public void getSynData() {
		String tablename = "bbs_c_" + carid;
		Statement st = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		int yearsize = years.size();
		for (int i = 0; i < yearsize; i++) {
			if (i != yearsize - 1) {
				sb.append("YEAR(bbs_ptime)=" + years.get(i) + " OR ");
			} else {
				sb.append("YEAR(bbs_ptime)=" + years.get(i));
			}
		}
		String sql = "SELECT bbs_title,bbs_content,YEAR(bbs_ptime),bbs_cs,bbs_sf,bbs_bbsid FROM " + tablename
				+ "  WHERE " + sb.toString();
		tablename = null;
		sb = null;
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			sql = null;
			while (rs.next()) {
				String bt = rs.getString(1);
				String nr = rs.getString(2);
				int year = rs.getInt(3);
				String city = rs.getString(4);
				int bbsid = rs.getInt(6);
				if (city.length() < 1) {
					String sf = rs.getString(5);
					if (sf.equals("上海") || sf.equals("北京") || sf.equals("天津") || sf.equals("重庆")) {
						city = "北京";
					} else {
						city = "";
					}
					sf = null;
				}
				for (String h : bt.split("\n|\\}\\{|\\?")) {
					isContains(h, year, city, bbsid, 1);
				}
				for (String h : nr.split("\n|\\}\\{|\\?")) {
					isContains(h, year, city, bbsid, 1);
				}
				bt = null;
				nr = null;
				city = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void getSynKb() {
		Statement st = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		int yearsize = years.size();
		for (int i = 0; i < yearsize; i++) {
			if (i != yearsize - 1) {
				sb.append("YEAR(kb_ptime)=" + years.get(i) + " OR ");
			} else {
				sb.append("YEAR(kb_ptime)=" + years.get(i));
			}
		}
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(
					"SELECT kb_content,YEAR(kb_ptime),kb_province,kb_kbid FROM autohome_syn.autohome_kb WHERE (kb_cid IN (SELECT autohome_autohomeId AS kb_cid FROM carvoice_core.core_brand_autohome WHERE autohome_autohomeClubId="
							+ carid + "))" + " AND (" + sb.toString() + ")");
			sb = null;
			while (rs.next()) {
				String nr = rs.getString(1);
				int year = rs.getInt(2);
				String city = rs.getString(3);
				int kbid = rs.getInt(4);
				if (city.length() < 1) {
					city = "";
				}
				for (String h : nr.split("\n|\\}\\{|\\?")) {
					isContains(h, year, city, kbid, 2);
				}
				nr = null;
				city = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void getSynNewsReply() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT reply_tzid,Year(reply_ptime),UPPER(reply_content) from autohome_other.other_news_reply";
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int reply_id = rs.getInt(1);// 帖子的ID
				int reply_year = rs.getInt(2);
				String content = rs.getString(3);
				for (String h : content.split("\n|\\}\\{|\\?")) {
					isContains(h, reply_year, "", reply_id, 3);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Title: checkSub
	 * @Description: 去除子集操做
	 * @param list
	 * @return
	 * @return List<Integer>
	 * @author Alexis
	 */
	public final List<Integer> checkSub(List<Integer> list, int type) {
		List<Integer> res = new ArrayList<>();
		res.addAll(list);
		Map<Integer, List<String>> map = type == 1 ? topicMap : topicMap2;
		for (int i = 0; i < list.size(); i++) {
			for (int j = i + 1; j < list.size(); j++) {
				List<String> s1 = map.get(list.get(i));
				List<String> s2 = map.get(list.get(j));
				int l1 = 0;
				int l2 = 0;
				StringBuffer sb1 = new StringBuffer();
				StringBuffer sb2 = new StringBuffer();
				for (String s : s1) {
					l1 += s.length();
					sb1.append(s);
				}
				for (String s : s2) {
					l2 += s.length();
					sb2.append(s);
				}
				if (l1 >= l2) {
					boolean flag = true;
					String c = sb1.toString();
					for (String s : s2) {
						flag = c.contains(s);
						if (!flag) {
							break;
						}
					}
					if (flag) {
						res.remove((Integer) list.get(j));
					}
				} else {
					boolean flag = true;
					String c = sb2.toString();
					for (String s : s1) {
						flag = c.contains(s);
						if (!flag) {
							break;
						}
					}
					if (flag) {
						res.remove((Integer) list.get(i));
					}
				}
				s1 = null;
				s2 = null;
			}
		}
		return res;
	}

	/**
	 * @Title: isContains
	 * @Description: TODO 判断给定的句子是否包含特定的关键词，包含则插入到数据库中
	 * @param content
	 * @param year
	 * @return void
	 * @author Alexis
	 */
	public void isContains(String content, int year, String city, int id, int type) {
		List<Integer> ids = acdatLocal.parseTextNotHit(content);
		if (ids.size() < 1) {
			return;
		}
		List<Integer> idsAfterWord1 = getHitForWord(ids, content, 1);// 去除子集处理
		if (idsAfterWord1 == null) {
			return;
		}
		if (isFilter) {
			List<Integer> ids2 = acdatLocal2.parseTextNotHit(content);
			List<Integer> idsAfterWord2 = getHitForWord(ids2, content, 2);
			if (ids2.size() < 1) {
				return;
			}
			if (idsAfterWord2 == null) {
				return;
			}
			int size1 = idsAfterWord1.size();
			int size2 = idsAfterWord2.size();
			for (int i = 0; i < size1; i++) {
				for (int j = 0; j < size2; j++) {
					String level = "";
					level = map.get(city);
					int k1 = idsAfterWord1.get(i);
					int k2 = idsAfterWord2.get(j);
					insert(content, year, topicMap.get(k1), topicMap2.get(k2), level, id, type, comment1.get(k1),
							comment2.get(k2));// 包含则插入
					level = null;
				}
			}
			idsAfterWord1 = null;
			idsAfterWord2 = null;
		} else {
			int size1 = idsAfterWord1.size();
			for (int i = 0; i < size1; i++) {
				String level = "";
				level = map.get(city);
				int k1 = idsAfterWord1.get(i);
				insert(content, year, topicMap.get(k1), null, level, id, type, comment1.get(k1), "");// 包含则插入
				level = null;
			}
		}

	}

	public List<Integer> getHitForWord(List<Integer> ids, String content, int type) {
		if (ids.size() == 0) {
			ids = null;
			return null;
		}
		Map<Integer, List<Integer>> topicInt = type == 1 ? topics : topics2;
		Set<Integer> set = new HashSet<>();
		for (int s : ids) {
			Set<Integer> ll = new HashSet<>(topicInt.get(s));
			set.addAll(ll);
		}
		ids = null;
		List<Integer> idList = new ArrayList<>(set);
		set = null;
		List<Integer> idTemp = new ArrayList<>(idList);
		Map<Integer, List<String>> topicString = type == 1 ? topicMap : topicMap2;
		for (int i : idTemp) {
			List<String> word = topicString.get(i);
			boolean flag = true;
			for (String w : word) {
				flag = content.contains(w);
				if (!flag) {
					break;
				}
			}
			if (!flag) {
				idList.remove((Integer) i);
			}
			word = null;
		}
		idTemp = null;
		List<Integer> hitList = checkSub(idList, type);
		idList = null;
		topicInt = null;
		topicString = null;
		return hitList;
	}

	/**
	 * @Title: insert
	 * @Description: TODO 插入数据库
	 * @param content
	 *            包含关键词的句子
	 * @param year
	 *            年份
	 * @param words
	 *            关键词集合
	 * @return void
	 * @author Alexis
	 */
	public void insert(String content, int year, List<String> words, List<String> words2, String level, int id,
			int type, String c1, String c2) {
		PreparedStatement ps = null;
		StringBuilder sb = new StringBuilder();
		StringBuilder temp = new StringBuilder();

		for (int i = 1; i < count + 1; i++) {
			sb.append("w" + i + ",");
			temp.append("?,");
		}
		sb.append("comment1,");
		temp.append("?,");
		if (isFilter) {
			for (int i = 1; i < count2 + 1; i++) {
				sb.append("t" + i + ",");
				temp.append("?,");
			}
			sb.append("comment2,");
			temp.append("?,");
		}
		String sql = "INSERT INTO " + database + "." + tablename + "(carid," + sb.toString()
				+ "year,content,area,pid,type) VALUES(?," + temp.toString() + "?,?,?,?,?" + ")";
		try {
			ps = conn198.prepareStatement(sql);
			ps.setInt(1, carid);
			for (int i = 1; i < count + 1; i++) {
				ps.setString(i + 1, words.get(i - 1));
			}
			ps.setString(count + 2, c1);
			// 7
			if (isFilter) {
				for (int i = 1 + count + 1; i < count + count2 + 2; i++) {
					ps.setString(i + 1, words2.get(i - count - 2));
				}
				ps.setString(count + 3 + count2, c2);
			}
			// 12
			ps.setInt(4 + count + count2, year);
			ps.setString(5 + count + count2, content);
			ps.setString(6 + count + count2, level);
			ps.setInt(7 + count + count2, id);
			ps.setInt(8 + count + count2, type);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void run() {
		if (bbs) {
			getSynData();
		}
		if (kb) {
			getSynKb();
		}
		System.out.println(carid + "\t" + "FINISH!");
	}

	public SenDao(int carid, Connection conn168, Connection conn198, String tablename, int count, int count2,
			String database, List<Integer> years, boolean bbs, boolean kb, boolean isFilter, Map<String, String> map,
			Map<Integer, List<Integer>> topics, Map<Integer, List<String>> topicMap,
			AhoCorasickDoubleArrayTrie<Integer> acdatLocal, Map<Integer, List<Integer>> topics2,
			Map<Integer, List<String>> topicMap2, AhoCorasickDoubleArrayTrie<Integer> acdatLocal2,
			Map<Integer, String> comment1, Map<Integer, String> comment2) {
		super();
		this.carid = carid;
		this.conn168 = conn168;
		this.conn198 = conn198;
		this.tablename = tablename;
		this.count = count;
		this.count2 = count2;
		this.database = database;
		this.years = years;
		this.bbs = bbs;
		this.kb = kb;
		this.isFilter = isFilter;
		this.map = map;
		this.topics = topics;
		this.topicMap = topicMap;
		this.acdatLocal = acdatLocal;
		this.topics2 = topics2;
		this.topicMap2 = topicMap2;
		this.acdatLocal2 = acdatLocal2;
		this.comment1 = comment1;
		this.comment2 = comment2;
	}

	public static void main(String[] args) throws Exception {
		long start = System.currentTimeMillis();
		String database = "word2vec";// 表所在数据库的名称
		String tablename = "test1215";// 表的名称，前面要添加具体的数据库
		int count = 4;// 关键词的数目
		int count2 = 4;// 关键词的数目
		String carpath = "car.txt";// 车款ID的文件路径，文件每个carid为一行
		String yearpath = "year.txt";// 年份的文件路径，每一年为一行
		boolean isBbs = true;// 是否选择BBS，默认选择TRUE，不选改为 false
		boolean isKb = true;
		boolean isNewsreply = true;
		boolean isFilter = true;// 是否包含word2.txt
		if (!isFilter) {
			count2 = 0;
		}
		Connection conn198 = ConnectionPool.getConnection("autohome_syn", "localhost");
		Connection conn168 = ConnectionPool.getConnection("autohome_syn", "192.169.0.168");
		SentenceUtil.createTable(tablename, count, count2, database, isFilter);
		SentenceUtil sen = new SentenceUtil();
		List<Integer> carids = sen.readCarID(carpath);
		Map<List<String>, String> words = sen.getWord("Projects_2016", "word1");
		Map<List<String>, String> words2 = sen.getWord("Projects_2016", "word2");

		Map<String, Integer> wordMap = new HashMap<String, Integer>();// word---对应的所有主题集合的ID
		Map<Integer, List<Integer>> topics = new HashMap<Integer, List<Integer>>();// 所有主题集合的ID
		Map<Integer, List<String>> topicMap = new HashMap<>();// 所有主题的集合
		Map<Integer, String> comentMap = new HashMap<>();// 所有主题的集合对应的Comment
		int index = 0;
		int i = 0;
		for (List<String> keyString : words.keySet()) {
			List<String> topic = new ArrayList<>();
			List<String> word = keyString;
			for (String s : word) {
				topic.add(s);
				if (s.length() > 0) {
					if (wordMap.containsKey(s)) {
						int key = wordMap.get(s);
						List<Integer> value = topics.get(key);
						value.add(i);
						topics.put(key, value);
					} else {
						List<Integer> list = new ArrayList<>();
						list.add(i);
						wordMap.put(s, index);
						topics.put(index, list);
						index++;
					}
				}
			}
			topicMap.put(i, topic);
			topic = null;
			comentMap.put(i, words.get(keyString));
			i++;
		}

		/* 双Trie树 */
		AhoCorasickDoubleArrayTrie<Integer> acdatLocal = new AhoCorasickDoubleArrayTrie<Integer>();
		acdatLocal.build(wordMap);

		Map<String, Integer> wordMap2 = new HashMap<String, Integer>();// word---对应的所有主题集合的ID
		Map<Integer, List<Integer>> topics2 = new HashMap<Integer, List<Integer>>();// 所有主题集合的ID
		Map<Integer, List<String>> topicMap2 = new HashMap<>();// 所有主题的集合
		Map<Integer, String> comentMap2 = new HashMap<>();// 所有主题的集合
		index = 0;
		i = 0;
		for (List<String> keyString : words2.keySet()) {
			List<String> topic = new ArrayList<>();
			List<String> word = keyString;
			for (String s : word) {
				topic.add(s);
				if (s.length() > 0) {
					if (wordMap2.containsKey(s)) {
						int key = wordMap2.get(s);
						List<Integer> value = topics2.get(key);
						value.add(i);
						topics2.put(key, value);
					} else {
						List<Integer> list = new ArrayList<>();
						list.add(i);
						wordMap2.put(s, index);
						topics2.put(index, list);
						index++;
					}
				}
			}
			topicMap2.put(i, topic);
			topic = null;
			comentMap2.put(i, words2.get(keyString));
			i++;
		}
		/* 双Trie树 */
		AhoCorasickDoubleArrayTrie<Integer> acdatLocal2 = new AhoCorasickDoubleArrayTrie<Integer>();
		acdatLocal2.build(wordMap2);
		List<Integer> years = sen.readYear(yearpath);
		Map<String, String> map = SentenceUtil.getCityLevel();
		ExecutorService fix = Executors.newFixedThreadPool(15);
		for (int carid : carids) {
			fix.execute(new SenDao(carid, conn168, conn198, tablename, count, count2, database, years, isBbs, isKb,
					isFilter, map, topics, topicMap, acdatLocal, topics2, topicMap2, acdatLocal2, comentMap,
					comentMap2));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				if (isNewsreply) {
					new SenDao(0, conn168, conn198, tablename, count, count2, database, years, isBbs, isKb, isFilter,
							map, topics, topicMap, acdatLocal, topics2, topicMap2, acdatLocal2, comentMap, comentMap2)
									.getSynNewsReply();
				}
				System.out.println("用时: " + (System.currentTimeMillis() - start) / (1000 * 60) + "Minute");
				break;
			} else {
				try {
					Thread.sleep(1000 * 60);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
